package Com.Hupochuan.Dal.Tab;

import java.util.Hashtable;
import java.util.List;
import java.util.Vector;

import Com.Hupochuan.Dal.DBManager.DBManager;
import Com.Hupochuan.Vo.Movie;
import Com.Hupochuan.Vo.Year;

public class MovieTab {
	/*
	 * 本类私有对象
	 */
	private DBManager<Movie> dbManager = new DBManager<Movie>(Movie.class);
	
	/*
	 * 单一实例
	 */
	private MovieTab(){}
	private static MovieTab movieTab = new MovieTab();
	public static MovieTab getObject(){
		return movieTab;
	}
	
	/*
	 * 查询电影数量
	 */
	public long findMovieCount(String key,Year year,long areaSortId,long typeId,long directorId,long actorId){
		
		//参数部分
		long movieCount = 0;
		StringBuffer sqlBuff = new StringBuffer();
		Hashtable<String,Object> paraHash = new Hashtable<String,Object>();
		
		//sql语句部分
		sqlBuff.append(" select count(id) as movieCount from Movie ");
		sqlBuff.append(" where ( name like ${key} or byname like ${key} ) ");
		
		//选择了年份
		if(year != null){
			sqlBuff.append(" 	and Movie.releaseDate>=${startDate} ");
			sqlBuff.append(" 	and Movie.releaseDate<=${endDate} ");
			
			//sql参数部分
			paraHash.put("startDate",year.getStartDate());
			paraHash.put("endDate",year.getEndDate());
		}
		
		//如果选择了 电影地区分类
		if(areaSortId != 0){
			sqlBuff.append(" and areaId in ( ");
			sqlBuff.append(" 	select id from Area ");
			sqlBuff.append(" 	where sortId=${areaSortId} ");
			sqlBuff.append(" ) ");
		}
		
		//如果选择了 电影的类型
		if(typeId != 0){
			sqlBuff.append(" and id in ( ");
			sqlBuff.append(" 	select movieId from MovieType ");
			sqlBuff.append(" 	where typeId=${typeId} ");
			sqlBuff.append(" ) ");
		}
		
		//如果选择了 电影的导演
		if(directorId != 0){
			sqlBuff.append(" and directorId=${directorId} ");
		}
		
		//如果选择了 电影的演员
		if(actorId != 0){
			sqlBuff.append(" and id in ( ");
			sqlBuff.append(" 	select movieId from MovieActor ");
			sqlBuff.append(" 	where actorId=${actorId} ");
			sqlBuff.append(" ) ");
		}
		
		//sql参数部分
		paraHash.put("key","%"+key+"%");
		paraHash.put("areaSortId",areaSortId);
		paraHash.put("typeId",typeId);
		paraHash.put("directorId",directorId);
		paraHash.put("actorId",actorId);
		
		//执行sql
		movieCount = dbManager.querySqlReturnLong(sqlBuff.toString(),paraHash);
		
		//返回列表
		return movieCount;
	}
	/*
	 * 查询电影列表
	 */
	public List<Movie> findMovieList(String key,Year year,long areaSortId,long typeId,long directorId,long actorId,long pageNo,long pageSize){
		
		//参数部分
		List<Movie> movieList = new Vector<Movie>();
		StringBuffer sqlBuff = new StringBuffer();
		Hashtable<String,Object> paraHash = new Hashtable<String,Object>();
		
		//sql语句部分
		sqlBuff.append(" select * from Movie ");
		sqlBuff.append(" where ( name like ${key} or byname like ${key} ) ");

		//选择了年份
		if(year != null){
			sqlBuff.append(" 	and Movie.releaseDate>=${startDate} ");
			sqlBuff.append(" 	and Movie.releaseDate<=${endDate} ");
			
			//sql参数部分
			paraHash.put("startDate",year.getStartDate());
			paraHash.put("endDate",year.getEndDate());
		}
		
		//如果选择了 电影地区分类
		if(areaSortId != 0){
			sqlBuff.append(" and areaId in ( ");
			sqlBuff.append(" 	select id from Area ");
			sqlBuff.append(" 	where sortId=${areaSortId} ");
			sqlBuff.append(" ) ");
		}
		
		//如果选择了 电影的类型
		if(typeId != 0){
			sqlBuff.append(" and id in ( ");
			sqlBuff.append(" 	select movieId from MovieType ");
			sqlBuff.append(" 	where typeId=${typeId} ");
			sqlBuff.append(" ) ");
		}
		
		//如果选择了 电影的导演
		if(directorId != 0){
			sqlBuff.append(" and directorId=${directorId} ");
		}
		
		//如果选择了 电影的演员
		if(actorId != 0){
			sqlBuff.append(" and id in ( ");
			sqlBuff.append(" 	select movieId from MovieActor ");
			sqlBuff.append(" 	where actorId=${actorId} ");
			sqlBuff.append(" ) ");
		}
		
		//顺序
		sqlBuff.append(" order by weight desc,releaseDate desc,id ");
		sqlBuff.append(" limit ${pageNo},${pageSize} ");
		
		//sql参数部分
		paraHash.put("key","%"+key+"%");
		paraHash.put("areaSortId",areaSortId);
		paraHash.put("typeId",typeId);
		paraHash.put("directorId",directorId);
		paraHash.put("actorId",actorId);
		paraHash.put("pageNo",(pageNo - 1) * pageSize);
		paraHash.put("pageSize",pageSize);
		
		//执行sql 
		movieList = this.dbManager.querySqlReturnVoList(sqlBuff.toString(),paraHash);
		
		//返回列表
		return movieList;
	}
	
	/*
	 * 最新电影
	 */
	public List<Movie> findNewMovieTop18List(){
		//参数部分
		List<Movie> NewMovieTop18List = new Vector<Movie>();
		StringBuffer sqlBuff = new StringBuffer();
		Hashtable<String,Object> paraHash = new Hashtable<String,Object>();
		
		//sql语句部分
		sqlBuff.append(" select * from Movie ");
		sqlBuff.append(" order by releaseDate desc,weight desc,id limit 0,18 ");
		
		//执行sql 
		NewMovieTop18List = this.dbManager.querySqlReturnVoList(sqlBuff.toString(),paraHash);
		
		//返回列表
		return NewMovieTop18List;
	}
	
	/*
	 * 获得本周排行榜
	 */
	public List<Movie> findTswkList(){
		//参数部分
		List<Movie> tswkList = new Vector<Movie>();
		StringBuffer sqlBuff = new StringBuffer();
		Hashtable<String,Object> paraHash = new Hashtable<String,Object>();
		
		//sql语句部分
		sqlBuff.append(" select id,name from Movie ");
		sqlBuff.append(" order by weight desc,releaseDate desc,id limit 0,20 ");
		
		//执行sql 
		tswkList = this.dbManager.querySqlReturnVoList(sqlBuff.toString(),paraHash);
		
		//返回列表
		return tswkList;
	}
	
	/*
	 * 获得电影对象
	 */
	public Movie findMovie(long id){
		//参数部分
		Movie movie = null;
		StringBuffer sqlBuff = new StringBuffer();
		Hashtable<String,Object> paraHash = new Hashtable<String,Object>();
		
		//sql语句部分
		sqlBuff.append(" select Movie.*, ");
		sqlBuff.append(" 	Director.name as directorName, ");
		sqlBuff.append(" 	Area.name as areaName ");
		sqlBuff.append(" from Movie  ");
		sqlBuff.append(" left join Director on Movie.directorId=Director.id ");
		sqlBuff.append(" left join Area on Movie.areaId=Area.id ");
		sqlBuff.append(" where Movie.id=${id} ");
		
		//sql参数部分
		paraHash.put("id",id);
		
		//从列表中获得对象
		movie = dbManager.querySqlReturnVo(sqlBuff.toString(),paraHash);
		
		//返回
		return movie;
	}
}
